package com.infodms.dms.dao.sales.storageManage;

import java.sql.ResultSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.infodms.dms.common.Utility;
import com.infodms.dms.dao.common.BaseDao;
import com.infodms.dms.dao.sales.ordermanage.reqquery.ReqQueryDao;
import com.infodms.dms.util.CommonUtils;
import com.infodms.dms.util.businessUtil.GetVinUtil;
import com.infoservice.po3.POFactory;
import com.infoservice.po3.POFactoryBuilder;
import com.infoservice.po3.bean.PO;
import com.infoservice.po3.bean.PageResult;

public class CheckVehicleQueryDAO extends BaseDao{
	public static Logger logger = Logger.getLogger(CheckVehicleQueryDAO.class);
	private static final CheckVehicleDAO dao = new CheckVehicleDAO ();
	public static final CheckVehicleDAO getInstance() {
		return dao;
	}
	/***
	 * 查询车辆验收历史记录(DLR)
	 * @param : 
	 * @return
	 * @throws Exception
	 */
	public static PageResult <Map<String,Object>> getCheckQueryDLR (String dealer_Id,String deliverystartDate, String deliveryendDate,String checkstartDate,String checkendDate, String vin, String dlvNo, int pageSize,int curPage){
		
		StringBuffer sql = new StringBuffer("");
		
		sql.append("SELECT *\n");
		sql.append("FROM (\n");
		sql.append("SELECT DISTINCT TMV.VEHICLE_ID,\n");
		sql.append("       G.GROUP_NAME, --车型名称\n");  
		sql.append("       TMVM.MATERIAL_CODE, --物料代码\n");  
		sql.append("       TMVM.MATERIAL_NAME, --物料名称\n");  
		sql.append("       TMV.VIN, --VIN\n");  
		sql.append("       TO_CHAR(TTD.DELIVERY_DATE, 'yyyy-MM-dd') DELIVERY_DATE, --发车日期\n");  
		sql.append("       TO_CHAR(TMV.STORAGE_DATE, 'yyyy-MM-dd') STORAGE_DATE, --验收日期\n");  
		sql.append("       TTI.INSPECTION_PERSON, --验收人\n");  
		sql.append("       TMW.WAREHOUSE_NAME,\n");
		sql.append("       TTD.DELIVERY_NO,\n");
		sql.append("	   TTI.VEHICLE_AREA\n");
		sql.append("  FROM TT_VS_INSPECTION          TTI,\n");  
		sql.append("       TM_VEHICLE             TMV,\n");  
		sql.append("       TM_VHCL_MATERIAL_GROUP G,\n");  
		sql.append("       TM_VHCL_MATERIAL       TMVM,\n");  
		sql.append("       TT_VS_DLVRY_MCH      TTDM,\n");  
		sql.append("       TT_VS_DLVRY_DTL     TTDD,\n");  
		sql.append("       TT_VS_DLVRY            TTD,\n"); 
		sql.append("	   TM_WAREHOUSE         TMW\n");

		sql.append(" WHERE TTI.VEHICLE_ID = TMV.VEHICLE_ID\n");  
		sql.append("   AND TMV.MODEL_ID = G.GROUP_ID\n");  
		sql.append("   AND TMV.MATERIAL_ID = TMVM.MATERIAL_ID\n");  
		sql.append("   AND TMV.VEHICLE_ID = TTDM.VEHICLE_ID\n");  
		sql.append("   AND TTDM.DELIVERY_DETAIL_ID = TTDD.DETAIL_ID\n");  
		sql.append("   AND TTDD.DELIVERY_ID = TTD.DELIVERY_ID\n"); 
		sql.append("   AND TMV.WAREHOUSE_ID = TMW.WAREHOUSE_ID\n");
		sql.append("   AND (TMV.DEALER_ID IN ("+dealer_Id+") OR TTD.RECEIVER IN("+dealer_Id+"))\n");  
		//发车日期
		if (null != deliverystartDate && !"".equals(deliverystartDate)) {
			sql.append("   AND TTD.DELIVERY_DATE >= TO_DATE('"+deliverystartDate+"','yyyy-MM-dd')\n");
		}
		if (null != deliveryendDate && !"".equals(deliveryendDate)) {
			sql.append("   AND TTD.DELIVERY_DATE <= (TO_DATE('"+deliveryendDate+"','yyyy-MM-dd')+1)\n");
		}
		//验收日期
		if (null != checkstartDate && !"".equals(checkstartDate)) {
			sql.append("   AND TMV.STORAGE_DATE >= TO_DATE('"+checkstartDate+"','yyyy-MM-dd')\n");
		}
		if (null != checkendDate && !"".equals(checkendDate)) {
			sql.append("   AND TMV.STORAGE_DATE <= (TO_DATE('"+checkendDate+"','yyyy-MM-dd')+1)\n");
		}
		//VIN
		if (null != vin && !"".equals(vin)) {
			sql.append(GetVinUtil.getVins(vin,"TMV")); 
		}
		if (null != dlvNo && !"".equals(dlvNo)) {
			sql.append("   AND TTD.DELIVERY_NO like UPPER('%" + dlvNo + "%') \n");
		}
		sql.append(") TMV\n");
		sql.append(" ORDER BY TMV.STORAGE_DATE DESC\n");
		
		return dao.pageQuery(sql.toString(), null,"com.infodms.dms.dao.sales.storageManage.CheckVehicleQueryDAO.getCheckQueryDLR",pageSize ,curPage );
	}
	
	/***
	 * 查询车辆验收详细历史记录:基本信息(DLR)
	 * @param : 
	 * @return
	 * @throws Exception
	 */
	public static Map<String,Object> getCheckVehicleQueryDLR_Detail(String vehicle_id){
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT TTI.VEHICLE_ID, --查询验收基本信息\n");
		sql.append("       TMV.VIN, --VIN\n");  
		sql.append("       TO_CHAR(TTD.DELIVERY_DATE, 'yyyy-MM-dd') DELIVERY_DATE, --发车日期\n");  
		sql.append("       G.GROUP_NAME, --车型\n");  
		sql.append("       TMV.ENGINE_NO, --发动机号\n");  
		sql.append("       TMV.COLOR, --颜色\n");  
		sql.append("       TO_CHAR(TTI.ARRIVE_DATE, 'yyyy-MM-dd') ARRIVE_DATE, --实际到车日期\n");  
		sql.append("       TTI.ARRIVE_TIME, --实际到车时间\n");  
		sql.append("       TTI.TRANSPORT_PERSON, --送车人员\n");  
		sql.append("       TTI.INSPECTION_NO, --验收单号\n");  
		sql.append("       TTI.INSPECTION_PERSON, --验收人员\n");  
		sql.append("       TTI.REMARK, --备注\n");  
		sql.append("       TMW.WAREHOUSE_NAME,\n");  
		sql.append("       TVDE.SENDCAR_ORDER_NUMBER,\n");  
		sql.append("       TDW.WAREHOUSE_NAME VEHICLE_AREA\n");  
		sql.append("  FROM TT_VS_INSPECTION       TTI,\n");  
		sql.append("       TT_VS_DLVRY_MCH        TTDM,\n");  
		sql.append("       Tt_Vs_Dlvry_Erp        tvde,\n");  
		sql.append("       TT_VS_DLVRY_DTL        TTDD,\n");  
		sql.append("       TT_VS_DLVRY            TTD,\n");  
		sql.append("       TM_VEHICLE             TMV,\n");  
		sql.append("       TM_VHCL_MATERIAL_GROUP G,\n");  
		sql.append("       Tm_Dealer_Warehouse    TDW,\n");  
		sql.append("       TM_WAREHOUSE           TMW\n");  
		sql.append(" WHERE TTI.VEHICLE_ID = " + vehicle_id + "\n");  
		sql.append("   AND TTI.VEHICLE_ID = TTDM.VEHICLE_ID\n");  
		sql.append("   AND TVDE.SENDCAR_HEADER_ID = TTDM.ERP_SENDCAR_ID\n");  
		sql.append("   AND TTDM.DELIVERY_DETAIL_ID = TTDD.DETAIL_ID\n");  
		sql.append("   AND TTDD.DELIVERY_ID = TTD.DELIVERY_ID\n");  
		sql.append("   AND TTI.VEHICLE_ID = TMV.VEHICLE_ID\n");  
		sql.append("   AND TMV.MODEL_ID = G.GROUP_ID\n");  
		sql.append("   AND TMV.WAREHOUSE_ID = TMW.WAREHOUSE_ID\n");  
		sql.append("   AND TDW.WAREHOUSE_ID = TMV.VEHICLE_AREA\n");


		return dao.pageQueryMap(sql.toString(), null, "com.infodms.dms.dao.sales.storageManage.CheckVehicleQueryDAO.getCheckVehicleQueryDLR_Detail");
	}
	
	/***
	 * 查询车辆验收历史记录(OEM)
	 * @param : 
	 * @return
	 * @throws Exception
	 */
public static PageResult <Map<String,Object>> getCheckQueryOEM (String orgId, String dealerCode,String deliverystartDate, String deliveryendDate,String checkstartDate,String checkendDate, String vin, String areaIds, int pageSize,int curPage){
		
		StringBuffer sql = new StringBuffer("");
		List<Object> params = new LinkedList<Object>();
		
		sql.append("SELECT *\n");
		sql.append("FROM (");
		sql.append("SELECT DISTINCT TMV.VEHICLE_ID,\n");
		sql.append("       TMD.DEALER_SHORTNAME,--经销商名称 \n");
		sql.append("       G.GROUP_NAME, --车型名称\n");  
		sql.append("       TMVM.MATERIAL_CODE, --物料代码\n");  
		sql.append("       TMVM.MATERIAL_NAME, --物料名称\n");  
		sql.append("       TMV.VIN, --VIN\n");  
		sql.append("       TMW.WAREHOUSE_NAME,\n");  
		sql.append("       TO_CHAR(TTD.DELIVERY_DATE, 'yyyy-MM-dd') DELIVERY_DATE, --发车日期\n");  
		sql.append("       TO_CHAR(TMV.STORAGE_DATE, 'yyyy-MM-dd') STORAGE_DATE, --验收日期\n");  
		sql.append("       TTI.INSPECTION_PERSON, --验收人\n");  
		sql.append("       TTI.VEHICLE_AREA\n");
		sql.append("  FROM TT_VS_INSPECTION          TTI,\n");  
		sql.append("       TM_VEHICLE             TMV,\n");  
		sql.append("       TM_VHCL_MATERIAL_GROUP G,\n");  
		sql.append("       TM_VHCL_MATERIAL       TMVM,\n");  
		sql.append("       TT_VS_DLVRY_MCH      TTDM,\n");  
		sql.append("       TT_VS_DLVRY_DTL     TTDD,\n");  
		sql.append("       TT_VS_DLVRY            TTD,\n");  
		sql.append("       TM_DEALER              TMD, \n");
		sql.append("       TM_WAREHOUSE TMW \n");

		sql.append(" WHERE TTI.VEHICLE_ID = TMV.VEHICLE_ID\n");  
		sql.append("   AND TMV.MODEL_ID = G.GROUP_ID\n");  
		sql.append("   AND TMV.MATERIAL_ID = TMVM.MATERIAL_ID\n");  
		sql.append("   AND TMV.VEHICLE_ID = TTDM.VEHICLE_ID\n");  
		sql.append("   AND TTDM.DELIVERY_DETAIL_ID = TTDD.DETAIL_ID\n");  
		sql.append("   AND TTDD.DELIVERY_ID = TTD.DELIVERY_ID\n");  
		sql.append("   AND TMV.DEALER_ID = TMD.DEALER_ID \n");
		sql.append("   AND TMV.WAREHOUSE_ID = TMW.WAREHOUSE_ID \n");
		//经销商
		if (null != dealerCode && !"".equals(dealerCode)) {
			sql.append(Utility.getConSqlByParamForEqual(dealerCode, params, "TMD", "DEALER_CODE"));
		}
		//发车日期
		if (null != deliverystartDate && !"".equals(deliverystartDate)) {
			sql.append("   AND TTD.DELIVERY_DATE >= TO_DATE('"+deliverystartDate+"','yyyy-MM-dd')\n");
		}
		if (null != deliveryendDate && !"".equals(deliveryendDate)) {
			sql.append("   AND TTD.DELIVERY_DATE <= (TO_DATE('"+deliveryendDate+"','yyyy-MM-dd')+1)\n");
		}
		//验收日期
		if (null != checkstartDate && !"".equals(checkstartDate)) {
			sql.append("   AND TMV.STORAGE_DATE >= TO_DATE('"+checkstartDate+"','yyyy-MM-dd')\n");
		}
		if (null != checkendDate && !"".equals(checkendDate)) {
			sql.append("   AND TMV.STORAGE_DATE <= (TO_DATE('"+checkendDate+"','yyyy-MM-dd')+1)\n");
		}
		//VIN
		if (null != vin && !"".equals(vin)) {
			sql.append(GetVinUtil.getVins(vin,"TMV")); 
		}
		if (null != areaIds && !"".equals(areaIds)) {
			sql.append("   AND TMV.AREA_ID IN ("+areaIds+")\n");
		}
		
		if(!CommonUtils.isNullString(orgId)) {
			sql.append(ReqQueryDao.limitOrg("TMD", "dealer_id", orgId)) ;
		}
		
		sql.append(") TMV");
		sql.append(" ORDER BY TMV.STORAGE_DATE DESC\n");
		
		return dao.pageQuery(sql.toString(), params,dao.getFunName(),pageSize ,curPage );
	}
	
	
	
	
	
	@Override
	protected PO wrapperPO(ResultSet rs, int idx) {
		// TODO Auto-generated method stub
		return null;
	}
}
